-- Indexes
CREATE INDEX idx_patients_name ON patients(last_name, first_name);
CREATE INDEX idx_appointments_doctor_date ON appointments(doctor_id, appointment_date);
CREATE INDEX idx_invoices_patient_status ON invoices(patient_id, payment_status);
CREATE INDEX idx_equipment_room ON equipment(room_id);

-- Function 1: Calculate the monthly income of the designated department.
CREATE OR REPLACE FUNCTION calculate_dept_monthly_revenue(
    p_dept_id INT,
    p_year INT,
    p_month INT
) RETURNS DECIMAL(12,2) AS $$
DECLARE
    total_revenue DECIMAL(12,2);
BEGIN
    SELECT COALESCE(SUM(i.total_amount), 0) INTO total_revenue
    FROM invoices i
    JOIN medical_records mr ON i.patient_id = mr.patient_id
    JOIN medical_staff ms ON mr.doctor_id = ms.staff_id
    WHERE ms.dept_id = p_dept_id
      AND EXTRACT(YEAR FROM i.invoice_date) = p_year
      AND EXTRACT(MONTH FROM i.invoice_date) = p_month
      AND i.payment_status = 'Paid';
    RETURN total_revenue;
END;
$$ LANGUAGE plpgsql;

-- Function 2: Query all medical records of designated patient.
CREATE OR REPLACE FUNCTION get_patient_medical_history(
    p_patient_id INT
) RETURNS TABLE (
    record_id INT,
    visit_date TIMESTAMP,
    doctor_name VARCHAR(100),
    diagnosed_disease VARCHAR(100),
    treatment_plan TEXT,
    satisfaction_score INT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        mr.record_id,
        mr.visit_date,
        CONCAT(ms.first_name, ' ', ms.last_name)::VARCHAR(100) AS doctor_name,
        COALESCE(d.disease_name, 'Undiagnosed')::VARCHAR(100) AS diagnosed_disease,
        mr.treatment_plan,
        mr.satisfaction_score
    FROM medical_records mr
    JOIN medical_staff ms ON mr.doctor_id = ms.staff_id
    LEFT JOIN diseases d ON mr.diagnosed_disease_id = d.disease_id
    WHERE mr.patient_id = p_patient_id
    ORDER BY mr.visit_date DESC;
END;
$$ LANGUAGE plpgsql;

-- Function 3: Check whether the doctor's appointment on the same day exceeds the upper limit.
CREATE OR REPLACE FUNCTION check_doctor_appointment_limit(
    p_doctor_id INT,
    p_appt_date TIMESTAMP
) RETURNS BOOLEAN AS $$
DECLARE
    appt_count INT;
    max_appointments INT := 10;
BEGIN
    SELECT COUNT(*) INTO appt_count
    FROM appointments
    WHERE doctor_id = p_doctor_id
      AND EXTRACT(YEAR FROM appointment_date) = EXTRACT(YEAR FROM p_appt_date)
      AND EXTRACT(MONTH FROM appointment_date) = EXTRACT(MONTH FROM p_appt_date)
      AND EXTRACT(DAY FROM appointment_date) = EXTRACT(DAY FROM p_appt_date)
      AND status != 'Cancelled';
    RETURN appt_count >= max_appointments;
END;
$$ LANGUAGE plpgsql;

-- Trigger 1: It is forbidden for doctors to make more than 10 appointments on the same day.
CREATE OR REPLACE FUNCTION trigger_check_appointment_limit()
RETURNS TRIGGER AS $$
BEGIN
    IF check_doctor_appointment_limit(NEW.doctor_id, NEW.appointment_date) THEN
        RAISE EXCEPTION 'Doctor % has reached maximum daily appointments (10)', NEW.doctor_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_appointment_limit
BEFORE INSERT OR UPDATE ON appointments
FOR EACH ROW
EXECUTE FUNCTION trigger_check_appointment_limit();

-- Trigger 2: Automatically set the room as unavailable when the equipment fails.
CREATE OR REPLACE FUNCTION trigger_update_room_availability()
RETURNS TRIGGER AS $$
DECLARE
    faulty_count INT;
    room_capacity INT;
BEGIN
    IF NEW.status = 'Faulty' THEN
        SELECT COUNT(*) INTO faulty_count
        FROM equipment
        WHERE room_id = NEW.room_id AND status = 'Faulty';

        IF faulty_count >= 1 THEN
            UPDATE treatment_rooms
            SET is_available = FALSE
            WHERE room_id = NEW.room_id;
        END IF;
    END IF;

    IF OLD.status = 'Faulty' AND NEW.status = 'Operational' THEN
        SELECT COUNT(*) INTO faulty_count
        FROM equipment
        WHERE room_id = NEW.room_id AND status = 'Faulty';

        IF faulty_count = 0 THEN
            UPDATE treatment_rooms
            SET is_available = TRUE
            WHERE room_id = NEW.room_id;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_room_availability
AFTER UPDATE ON equipment
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION trigger_update_room_availability();

-- Trigger 3: Update patient satisfaction after bill payment.
CREATE OR REPLACE FUNCTION trigger_update_patient_satisfaction()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.payment_status != 'Paid' AND NEW.payment_status = 'Paid' THEN
        UPDATE medical_records
        SET satisfaction_score = LEAST(satisfaction_score + 1, 5)
        WHERE patient_id = NEW.patient_id
          AND visit_date >= NEW.invoice_date - INTERVAL '7 days'; -- 7天内就诊记录
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_satisfaction
AFTER UPDATE ON invoices
FOR EACH ROW
WHEN (OLD.payment_status IS DISTINCT FROM NEW.payment_status)
EXECUTE FUNCTION trigger_update_patient_satisfaction();